/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package wargame.dao;

import connectionmanager.DB;
import wargame.entity.AnalyticEmployeeUtilisation;

/**
 *
 * @author Clarence
 */
public class AnalyticEmployeeUtilisationDAO {

    public static AnalyticEmployeeUtilisation retrieveAnalyticEmployeeUtilisation(int analytic_employee_utilisation_id) {
        String sql = "SELECT `analytic_employee_utilisation_id`, `player_id`, `employee_available_hours`, `employee_hours_used`, `turn` FROM `analytic_employee_utilisation` WHERE analytic_employee_utilisation_id = '" + analytic_employee_utilisation_id + "'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        AnalyticEmployeeUtilisation aeu = null;
        if (db.next()) {
            aeu = populate(db);
        }
        db.close();
        return aeu;
    }

    private static AnalyticEmployeeUtilisation populate(DB db) {
        AnalyticEmployeeUtilisation aeu = new AnalyticEmployeeUtilisation();
        aeu.setAnalytic_employee_utilisation_id(db.getInt("analytic_employee_utilisation_id"));
        aeu.setPlayer_id(db.getInt("player_id"));
        aeu.setEmployee_available_hours(db.getInt("employee_available_hours"));
        aeu.setEmployee_hours_used(db.getInt("employee_hours_used"));
        aeu.setTurn(db.getInt("turn"));
        return aeu;
    }

    public static int insertAnalyticEmployeeUtilisation(AnalyticEmployeeUtilisation aeu) {
        String sql = "INSERT INTO analytic_employee_utilisation (`player_id`, `employee_available_hours`, `employee_hours_used`, `turn`) VALUES ('" + aeu.getPlayer_id() + "', '" + aeu.getEmployee_available_hours() + "', '" + aeu.getEmployee_hours_used() + "', '" + aeu.getTurn() + "');";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        int lastInsertedId = db.getLastInsertedID();
        db.close();
        return lastInsertedId;
    }

    public static void updateAnalyticEmployeeUtilisation(AnalyticEmployeeUtilisation aeu) {
        String sql = "UPDATE analytic_employee_utilisation SET `player_id` = '" + aeu.getPlayer_id() + "', `employee_available_hours` = '" + aeu.getEmployee_available_hours() + "', `employee_hours_used` = '" + aeu.getEmployee_hours_used() + "', `turn` = '" + aeu.getTurn() + "' WHERE analytic_employee_utilisation_id = '" + aeu.getAnalytic_employee_utilisation_id() + "';";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void deleteAnalyticEmployeeUtilisation(int analytic_employee_utilisation_id) {
        String sql = "DELETE FROM analytic_employee_utilisation WHERE analytic_employee_utilisation_id = '" + analytic_employee_utilisation_id + "'";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static double getEmployeeUtilisation(int player_id, int turn) {
        String sql = "SELECT `analytic_employee_utilisation_id`, `player_id`, `employee_available_hours`, `employee_hours_used`, `turn` FROM analytic_employee_utilisation WHERE player_id = '" + player_id + "' AND turn = '" + turn + "'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        double employee_utilisation = -1;
        if (db.next()) {
            double employee_hours_used = db.getInt("employee_hours_used");
            double employee_available_hours = db.getInt("employee_available_hours");

            if (employee_available_hours != 0) {
                employee_utilisation = employee_hours_used / employee_available_hours;
            }
            else {
                employee_utilisation = 1;
            }
        }
        return employee_utilisation;

    }

    public static int getMaxTurn() {
        String sql = "SELECT turn FROM analytic_employee_utilisation ORDER BY turn DESC Limit 0,1";
        DB db = new DB();
        db.connect();
        db.query(sql);
        int max_turn = 0;
        if (db.next()) {
            max_turn = db.getInt("turn");
        }
        return max_turn;
    }
}
